<?php

include 'DBLibrary/config.php';
include 'DBLibrary/openDB.php';
$codesecsemyear = $_POST['codesecsemyear'];
mysql_select_db($dbname);

require_once 'Spreadsheet/Excel/Writer.php';

// Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();

$rowedit = mysql_fetch_array(mysql_query("SELECT DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 8 HOUR),'%y-%m-%d-%h-%i-%s') as date"));

// sending HTTP headers
$workbook->send('Attendance-' . $rowedit['date'] . '.xls');

// Creating a worksheet
$worksheet = & $workbook->addWorksheet('Attendance List');

//format of text
$format_left = & $workbook->addFormat();
$format_left->setAlign('left');
$format_left->setAlign('middle');
$format_left->setFontFamily('Calibri');
$format_left->setSize('11');

$format_center_bold = & $workbook->addFormat();
$format_center_bold->setAlign('center');
$format_center_bold->setAlign('middle');
$format_center_bold->setBold('1');
$format_center_bold->setFontFamily('Calibri');
$format_center_bold->setSize('13');

//$format->setColor($inc);
$format_left_red = & $workbook->addFormat();
$format_left_red->setAlign('left');
$format_left_red->setAlign('middle');
$format_left_red->setFontFamily('Calibri');
$format_left_red->setSize('11');
$format_left_red->setColor('10');

$format_left_blue = & $workbook->addFormat();
$format_left_blue->setAlign('left');
$format_left_blue->setAlign('middle');
$format_left_blue->setFontFamily('Calibri');
$format_left_blue->setSize('11');
$format_left_blue->setColor('4');

$format_left_green = & $workbook->addFormat();
$format_left_green->setAlign('left');
$format_left_green->setAlign('middle');
$format_left_green->setFontFamily('Calibri');
$format_left_green->setSize('11');
$format_left_green->setColor('3');
/////////////////////////////////SELECT DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 8 HOUR),'%y-%m-%d-%h-%i-%s')

$querytext = "select * from coursetable where codesecsemyear='" . $codesecsemyear . "'";
$resulttext = mysql_query($querytext);
$text = mysql_fetch_array($resulttext);
$nummeet = $text['num_of_meeting'];
/////////////////
$querystud = "SELECT * FROM enrollment e left join student s on e.studidno=s.studidno where codesecsemyear='" . $codesecsemyear . "' order by s.lname, s.fname";
$allstud = mysql_query($querystud);
$ctr = 0;
$numrows = mysql_num_rows($allstud);
///////////////////
$queryattend = "select * from attendance where codesecsemyear='" . $codesecsemyear . "'";
$resultattend = mysql_query($queryattend);
//////number of entries [][][][][]
$attendnum = mysql_num_rows($resultattend);

for ($y = 0; $y < $attendnum; $y++) {
    mysql_select_db($dbname);
    $met = $y + 1;
    $queryattend = "select * from attendance where codesecsemyear='" . $codesecsemyear . "' and meeting_num='" . $met . "'";
    $resultattend = mysql_query($queryattend);
    $attend = mysql_fetch_array($resultattend);
    $status = $attend['status'];
    $statusarray = explode(";", $status);
    for ($x = 0; $x < $numrows; $x++) {
        $arratend[$x][$y] = $statusarray[$x];

        //echo $statusarray[$x];
    }
    //echo "<br>";
}




//column width
$worksheet->setColumn(0, 0, 4);
$worksheet->setColumn(1, 1, 25);
$worksheet->setColumn(2, $nummeet + 4, 3);

$worksheet->write(0, 0, 'No', $format_center_bold);
$worksheet->write(0, 1, 'Full Name', $format_center_bold);
$worksheet->write(1, 0, 'MM', $format_center_bold);
$worksheet->write(2, 0, 'DD', $format_center_bold);
$worksheet->write(3, 0, 'YY', $format_center_bold);

for ($x = 1; $x <= $nummeet; $x++) {
    $worksheet->write(0, $x + 1, $x, $format_center_bold);
}
$worksheet->write(0, $nummeet + 2, 'TA', $format_center_bold);
$worksheet->write(0, $nummeet + 3, 'TL', $format_center_bold);
$worksheet->write(0, $nummeet + 4, 'TP', $format_center_bold);

for ($ct = 1; $nummeet >= $ct; $ct++) {
    $querydate = "select MONTH(date) as mm, DAY(date) as dd, DATE_FORMAT(date,'%y') as yy from attendance where codesecsemyear='" . $codesecsemyear . "' and meeting_num='" . $ct . "'";
    $resultdate = mysql_query($querydate);
    $datetitle = mysql_fetch_array($resultdate);
    $worksheet->write(1, $ct + 1, $datetitle['mm'], $format_left);
    $worksheet->write(2, $ct + 1, $datetitle['dd'], $format_left);
    $worksheet->write(3, $ct + 1, $datetitle['yy'], $format_left);
}
//SELECT MONTH(DATE_ADD(NOW(), INTERVAL 8 HOUR)) as mm, YEAR(DATE_ADD(NOW(), INTERVAL 8 HOUR)) as yyyy, DAY(DATE_ADD(NOW(), INTERVAL 8 HOUR)) as dd
//////////////////
for ($x = 1; $x <= $numrows; $x++) {
    $worksheet->write($x + 3, 0, $x, $format_center);
}

$ctr = 2;
while ($row = mysql_fetch_array($allstud)) {
    $worksheet->write($ctr + 2, 1, $row['lname'] . ", " . $row['fname'] . " " . $row['mname'], $format_left);

    $ctr++;
}
/////////////////////$worksheet->write($ctr, 1, $row['lname'], $format_left);


for ($x = 0; $x < $numrows; $x++) {
    $TP = 0;
    $TA = 0;
    $TL = 0;
    for ($y = 0; $y < $attendnum; $y++) {
        if ($arratend[$x][$y] == 'A') {
            $worksheet->write($x + 4, $y + 2, $arratend[$x][$y], $format_left_red);
            $TA++;
        }
        if ($arratend[$x][$y] == 'L') {
            $worksheet->write($x + 4, $y + 2, $arratend[$x][$y], $format_left_blue);
            $TL++;
        }
        if ($arratend[$x][$y] == 'P') {
            $worksheet->write($x + 4, $y + 2, $arratend[$x][$y], $format_left_green);
            $TP++;
        }
    }

    $worksheet->write($x + 4, +$nummeet + 2, $TA, $format_left);


    $worksheet->write($x + 4, +$nummeet + 3, $TL, $format_left);


    $worksheet->write($x + 4, +$nummeet + 4, $TP, $format_left);
}
/*
  for ($x = 0; $x < $numrows; $x++)
  for ($y = 0; $y < $nummeet - $attendnum; $y++)
  $worksheet->write($x + 4, $y + 7, 'X', $format_left);

 */


/////////////////////
$workbook->close();
include 'DBLibrary/closeDB.php';
?>